package com.xscha.tableforge.repository;

import com.xscha.tableforge.model.entity.RelationMeta;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

import static com.xscha.tableforge.jooq.tables.RelationMetaTable.RELATION_META;
import static com.xscha.tableforge.jooq.tables.FieldMetaTable.FIELD_META;
import static org.jooq.impl.DSL.*;

@Repository
public class RelationMetaRepository {

    private final DSLContext dsl;

    @Autowired
    public RelationMetaRepository(DSLContext dsl) {
        this.dsl = dsl;
    }

    public Optional<RelationMeta> findById(String id) {
        Record record = dsl.select()
                .from(RELATION_META)
                .where(RELATION_META.ID.eq(id))
                .and(RELATION_META.DELETED_TIME.isNull())
                .fetchOne();
        
        return Optional.ofNullable(record).map(this::mapToRelationMeta);
    }

    public Optional<RelationMeta> findByFieldId(String fieldId) {
        Record record = dsl.select()
                .from(RELATION_META)
                .where(RELATION_META.FIELD_ID.eq(fieldId))
                .and(RELATION_META.DELETED_TIME.isNull())
                .fetchOne();
        
        return Optional.ofNullable(record).map(this::mapToRelationMeta);
    }

    public List<RelationMeta> findAllByTableId(String tableId) {
        Result<Record> records = dsl.select()
                .from(RELATION_META)
                .join(FIELD_META)
                .on(RELATION_META.FIELD_ID.eq(FIELD_META.ID))
                .where(FIELD_META.TABLE_ID.eq(tableId))
                .and(RELATION_META.DELETED_TIME.isNull())
                .fetch();
        
        return records.stream()
                .map(this::mapToRelationMeta)
                .collect(Collectors.toList());
    }

    public List<RelationMeta> findAllByTargetTableId(String targetTableId) {
        Result<Record> records = dsl.select()
                .from(RELATION_META)
                .where(RELATION_META.TARGET_TABLE_ID.eq(targetTableId))
                .and(RELATION_META.DELETED_TIME.isNull())
                .fetch();
        
        return records.stream()
                .map(this::mapToRelationMeta)
                .collect(Collectors.toList());
    }

    public RelationMeta save(RelationMeta relationMeta) {
        dsl.insertInto(RELATION_META)
                .set(RELATION_META.ID, relationMeta.getId())
                .set(RELATION_META.FIELD_ID, relationMeta.getFieldId())
                .set(RELATION_META.TARGET_TABLE_ID, relationMeta.getTargetTableId())
                .set(RELATION_META.TARGET_FIELD_ID, relationMeta.getTargetFieldId())
                .set(RELATION_META.DISPLAY_FIELD_ID, relationMeta.getDisplayFieldId())
                .set(RELATION_META.RELATION_TYPE, relationMeta.getRelationType())
                .set(RELATION_META.CASCADE_DELETE, relationMeta.getCascadeDelete())
                .set(RELATION_META.VERSION, relationMeta.getVersion())
                .set(RELATION_META.CREATED_TIME, relationMeta.getCreatedTime())
                .set(RELATION_META.CREATED_BY, relationMeta.getCreatedBy())
                .execute();
        
        return relationMeta;
    }

    public RelationMeta update(RelationMeta relationMeta) {
        dsl.update(RELATION_META)
                .set(RELATION_META.TARGET_TABLE_ID, relationMeta.getTargetTableId())
                .set(RELATION_META.TARGET_FIELD_ID, relationMeta.getTargetFieldId())
                .set(RELATION_META.DISPLAY_FIELD_ID, relationMeta.getDisplayFieldId())
                .set(RELATION_META.RELATION_TYPE, relationMeta.getRelationType())
                .set(RELATION_META.CASCADE_DELETE, relationMeta.getCascadeDelete())
                .set(RELATION_META.VERSION, relationMeta.getVersion())
                .set(RELATION_META.LAST_MODIFIED_TIME, relationMeta.getLastModifiedTime())
                .set(RELATION_META.LAST_MODIFIED_BY, relationMeta.getLastModifiedBy())
                .where(RELATION_META.ID.eq(relationMeta.getId()))
                .execute();
        
        return relationMeta;
    }

    public void delete(String id) {
        dsl.update(RELATION_META)
                .set(RELATION_META.DELETED_TIME, LocalDateTime.now())
                .where(RELATION_META.ID.eq(id))
                .execute();
    }

    private RelationMeta mapToRelationMeta(Record record) {
        RelationMeta relationMeta = new RelationMeta();
        relationMeta.setId(record.get(RELATION_META.ID));
        relationMeta.setFieldId(record.get(RELATION_META.FIELD_ID));
        relationMeta.setTargetTableId(record.get(RELATION_META.TARGET_TABLE_ID));
        relationMeta.setTargetFieldId(record.get(RELATION_META.TARGET_FIELD_ID));
        relationMeta.setDisplayFieldId(record.get(RELATION_META.DISPLAY_FIELD_ID));
        relationMeta.setRelationType(record.get(RELATION_META.RELATION_TYPE));
        relationMeta.setCascadeDelete(record.get(RELATION_META.CASCADE_DELETE));
        relationMeta.setVersion(record.get(RELATION_META.VERSION));
        relationMeta.setCreatedTime(record.get(RELATION_META.CREATED_TIME));
        relationMeta.setCreatedBy(record.get(RELATION_META.CREATED_BY));
        relationMeta.setLastModifiedTime(record.get(RELATION_META.LAST_MODIFIED_TIME));
        relationMeta.setLastModifiedBy(record.get(RELATION_META.LAST_MODIFIED_BY));
        relationMeta.setDeletedTime(record.get(RELATION_META.DELETED_TIME));
        return relationMeta;
    }
}